package com.itqf.dao.impl;

import com.itqf.connecter.DbConnection;
import com.itqf.dao.AddressDao;
import com.itqf.entity.Address;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AddressDaoImpl implements AddressDao {
    @Override

    public List<Address> selectAddressesByUserno(int userno) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "SELECT a_id as aid , userno as userno , a_name as aname , phonenumber as phonenumber , a_detail as adetail , a_state as astate from address where userno = ? order by a_state desc;";

        List<Address> tl = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,userno);
            rs = ps.executeQuery();
            while(rs.next()) {
                int aid = rs.getInt(1);
                String aname = rs.getString(3);
                String phonenumber = rs.getString(4);
                String adetail = rs.getString(5);
                int astate = rs.getInt(6);
                Address address = new Address(aid,userno,aname,phonenumber,adetail,astate);
                tl.add(address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
        return tl;
    }


    @Override
    public void insertAddress(Address address) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql ="insert into address(userno,a_name,phonenumber,a_detail,a_state) value (?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,address.getUserNo());
            ps.setString(2,address.getAname());
            ps.setString(3,address.getPhonenumber());
            ps.setString(4,address.getAdetail());
            ps.setInt(5,0);
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
//        return rows;
    }




    @Override
    public void deleteAddressByAid(String aid) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql  = "delete from address where a_id = ?;";
        try {

            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.valueOf(aid));
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
//        return rows;
    }



    @Override
    public void updateAddressToDefault(String aid) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "update address set a_state = 1 where a_id = ?";
        try {

            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.valueOf(aid));
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }


    @Override
    public void updateAddressToCommons(String aid, int userno) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "update address set a_state = 0 where a_id != ? and userno = ?";
        try {

            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.valueOf(aid));
            ps.setInt(2,userno);
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }

    @Override

    public void updateAddress(Address address) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "update address set a_state = ?, a_name = ?,phonenumber = ? ,a_detail = ? where a_id = ?;";
        try {

            ps = conn.prepareStatement(sql);
            ps.setInt(1,address.getAstate());
            ps.setString(2,address.getAname());
            ps.setString(3,address.getPhonenumber());
            ps.setString(4,address.getAdetail());

            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);

    }

    @Override
    public Address selectAddressesByAddressId(int addressId) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "SELECT a_id as aid , userno as userno , a_name as aname , phonenumber as phonenumber , a_detail as adetail ,a_state as astate from address where a_id = ? order by a_state desc;";

        Address address =null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,addressId);
            rs = ps.executeQuery();
            while(rs.next()) {
                int aid = rs.getInt(1);
                int userNo = rs.getInt(2);
                String aname = rs.getString(3);
                System.out.println(rs.getString(3));
                System.out.println(rs.getString(4));
                System.out.println(rs.getString(5));
                System.out.println(rs.getInt(6));
                String phonenumber = rs.getString(4);
                String adetail = rs.getString(5);
                int astate = rs.getInt(6);
                address = new Address(aid,userNo,aname,phonenumber,adetail,astate);
                System.out.println(address.toString());
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
        return address;
    }
}
